package edu.yuhf.dao;

import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import edu.yuhf.dao.iface.UserDao;
import edu.yuhf.domain.Page;
import edu.yuhf.domain.User;
import edu.yuhf.domain.UserDetail;
import edu.yuhf.web.dto.UserForm;

@Repository
public class UserJdbcDaoImpl implements UserDao {
	
	@Resource
	private JdbcTemplate jdbcTemplate;
	@Override
	public int queryUserByNameAndPwd(User user) {
		int rowNumber=0;
		String sql="select count(*) from users where name=? and password=?";
		rowNumber=jdbcTemplate.queryForObject(sql, Integer.class,new Object[] {user.getName(),user.getPassword()});
		return rowNumber;
	}
	@Override
	public int insertUser(User user) {
		int userId=0;
		int rowNumber=0;
		String sql="insert into users values(users_id.nextval,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder(); 
		rowNumber=jdbcTemplate.update((conn)->{
			PreparedStatement psmt=conn.prepareStatement(sql, new String[] {"id"});
			psmt.setString(1, user.getName());
			psmt.setString(2, user.getPassword());
			psmt.setString(3, user.getSex());
			psmt.setString(4, user.getEmail());
			return psmt;
		}, keyHolder);
		userId=keyHolder.getKey().intValue();
		if(rowNumber==1) {
			return userId;
		}
		return 0;
	}

	public int insertUserDetail(int userId,UserDetail userDetail) {
		int rowNumber=0;
		String sql="insert into userDetail values(users_id.nextval,?,?,?)";
		rowNumber=jdbcTemplate.update(sql, new Object[] {userDetail.getNativePlaceCode(),userDetail.getHobbyCode(),userId});
		return rowNumber;
	}
	@Override
	public List<UserForm> queryUserAll() {
		List<UserForm> list=new ArrayList<>();
		String sql="select u.id,u.name,u.password,u.sex,u.email,np.name,ud.hobby_code from users u left outer join Userdetail ud on u.id=ud.userid " + 
				"left outer join nativeplace np on ud.nativeplace_code=np.code";
		list=jdbcTemplate.query(sql, (rs,index)->
			new UserForm(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7))
		);
		return list;
	}
	@Override
	public void queryUserForPage(Page<UserForm> page, String keyword) {
		List<UserForm> list=new ArrayList<>();
		Object[] params=null;
		StringBuffer sql=new StringBuffer("");
		sql.append("select * from (select bt.*,rowNum rn from (");
		sql.append("select u.id id,u.name name,u.password password,u.sex sex,u.email email,np.name nativePlaceName,ud.hobby_code hobbyCode ");
		sql.append("from users u left outer join Userdetail ud on u.id=ud.userid ");
		if("".equals(keyword)) {
			sql.append("left outer join nativeplace np on ud.nativeplace_code=np.code order by id");
			params=new Object[] {page.getCurrentPage(),page.getRowNumber(),page.getCurrentPage(),page.getRowNumber()};
		}else {
			sql.append("left outer join nativeplace np on ud.nativeplace_code=np.code where u.name like ? order by id");
			params=new Object[] {"%"+keyword+"%",page.getCurrentPage(),page.getRowNumber(),page.getCurrentPage(),page.getRowNumber()};
		}
		sql.append(") bt where rowNum<=(?)*?) mt where rn>(?-1)*?");
		list=jdbcTemplate.query(sql.toString(), (rs,index)->
			new UserForm(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7)),
		params);
		page.setPageList(list);
	}

}
